package com.bogo.dbtable.service.dbtable.serviceImpl.impl;

import com.bogo.dbtable.handler.DatabaseOperationException;
import com.bogo.dbtable.modelVo.table.TableVo;
import com.bogo.dbtable.service.dbtable.domain.Field;
import com.bogo.dbtable.service.dbtable.domain.Table;
import com.bogo.dbtable.service.dbtable.mapper.TableMapper;
import com.bogo.dbtable.service.dbtable.serviceImpl.TableService;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.Date;
import java.util.List;

/**
 * @Author: CHICHI
 * @Date: 2025-05-08-14:07
 * @Description:
 */
@Slf4j
@Service
public class TableServiceImpl implements TableService {
    @Autowired
    private TableMapper tableMapper;
    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String user;

    @Value("${spring.datasource.password}")
    private String password;
    @Override
    public void createTable(Table table) throws SQLException {
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                    .append(table.getTableName()).append(" (");

            boolean firstColumn = true;
            for (Field field : table.getFields()) {
                if (!firstColumn) {
                    sql.append(", ");
                }
                firstColumn = false;

                sql.append(field.getFieldName())
                        .append(" ").append(field.getFieldType());

                // 处理字段长度和小数位数
                if (field.getFieldLength() > 0) {
                    sql.append("(").append(field.getFieldLength());

                    // 如果有小数位数（fieldDecimal > 0），则添加
                    if (field.getFieldDecimal() != null && field.getFieldDecimal() > 0) {
                        sql.append(",").append(field.getFieldDecimal());
                    }
                    sql.append(")");
                }

                // 处理是否允许NULL
                sql.append(field.getIsNull() == 1 ? " NULL" : " NOT NULL");

                // 处理是否自增
                if (field.getIsAddId() == 1) {
                    sql.append(" AUTO_INCREMENT");
                }

                // 处理是否主键
                if (field.getIsKey() == 1) {
                    sql.append(" PRIMARY KEY");
                }

                // 添加字段注释
                if (field.getFieldComment() != null && !field.getFieldComment().isEmpty()) {
                    sql.append(" COMMENT '").append(field.getFieldComment()).append("'");
                }
            }
            sql.append(")");
            stmt.executeUpdate(sql.toString());
            //将fields序列化成json格式
            String fieldsJson = new ObjectMapper().writeValueAsString(table.getFields());
            tableMapper.insertData(table.getTableName(),fieldsJson,table.getTableComment(),new Date());
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }catch (DataAccessException de){
            // 检查是否是列定义错误
            if (de.getCause() instanceof SQLException) {
                SQLException sqlEx = (SQLException) de.getCause();
                if (sqlEx.getMessage().contains("Incorrect column specifier for column")) {
                    throw new DatabaseOperationException("ID 列定义不正确", 10011);
                }
            }
            throw new DatabaseOperationException("数据库操作失败: " + de.getMessage(), 10010);
        }
    }

    @Override
    public List<TableVo> getTable() {
        List<TableVo> tables = tableMapper.getTable();
        //将fieldsjson反序列化List<Field>
        for (TableVo table : tables) {
            try {
                List<Field> fields = new ObjectMapper().readValue(table.getFieldContent(), List.class);
                table.setFields(fields);
            } catch (JsonProcessingException e) {
                throw new RuntimeException(e);
            }
        }
        log.info("getTable:{}", tables);
        return tables;
    }

    @Override
    public void updateTable(String tableName, String fieldName) {
        log.info("tableName:{},fieldName:{}", tableName,fieldName);
        TableVo tableVos=tableMapper.getTableBytableName(tableName);
        log.info("updateTable:{}", tableVos);
        try {
            // 修复这里 - 使用TypeReference指定完整类型
            List<Field> fields = new ObjectMapper().readValue(
                    tableVos.getFieldContent(),
                    new TypeReference<List<Field>>() {}
            );
            tableVos.setFields(fields);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
        //遍历fields如果有fieldName传入一样的则在修改的这张表中进行删除没有则添加
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            boolean fieldExists = false;

            // 先检查字段是否存在
            for (Field field : tableVos.getFields()) {
                if (field.getFieldName().equals(fieldName)) {
                    fieldExists = true;
                    break;
                }
            }

            if (fieldExists) {
                // 字段存在，执行删除操作
                // 如果是主键，需要先删除主键约束（如果存在）
                if (isPrimaryKeyField(tableName, fieldName, conn)) {
                    String dropPkSql = "ALTER TABLE " + tableName + " DROP PRIMARY KEY";
                    stmt.executeUpdate(dropPkSql);
                }

                String dropColumnSql = "ALTER TABLE " + tableName +
                        " DROP COLUMN " + fieldName;
                stmt.executeUpdate(dropColumnSql);

                // 从字段列表中移除该字段
                tableVos.getFields().removeIf(field -> field.getFieldName().equals(fieldName));
            } else {
                // 字段不存在，执行添加操作
                Field newField = new Field(); // 假设这是你的字段对象

                // 构建添加字段的SQL
                StringBuilder addColumnSql = new StringBuilder("ALTER TABLE ")
                        .append(tableName)
                        .append(" ADD COLUMN ")
                        .append(newField.getFieldName())
                        .append(" ")
                        .append(newField.getFieldType());

                // 处理字段长度和小数位数
                if (newField.getFieldLength() > 0) {
                    addColumnSql.append("(").append(newField.getFieldLength());

                    // 如果有小数位数（fieldDecimal > 0），则添加
                    if (newField.getFieldDecimal() != null && newField.getFieldDecimal() > 0) {
                        addColumnSql.append(",").append(newField.getFieldDecimal());
                    }
                    addColumnSql.append(")");
                }

                // 处理是否允许NULL
                addColumnSql.append(newField.getIsNull() == 1 ? " NULL" : " NOT NULL");

                // 处理自动递增
                if (newField.getIsAddId() == 1) {
                    addColumnSql.append(" AUTO_INCREMENT");
                }

                // 如果有注释
                if (newField.getFieldComment() != null && !newField.getFieldComment().isEmpty()) {
                    addColumnSql.append(" COMMENT '").append(newField.getFieldComment()).append("'");
                }

                stmt.executeUpdate(addColumnSql.toString());

                // 处理主键（需要在添加字段后单独设置）
                if (newField.getIsKey() == 1) {
                    String addPkSql = "ALTER TABLE " + tableName +
                            " ADD PRIMARY KEY (" + newField.getFieldName() + ")";
                    stmt.executeUpdate(addPkSql);
                }
                // 将新字段添加到字段列表
                tableVos.getFields().add(newField);
            }

            // 更新表的元数据信息
            String fieldContent = new ObjectMapper().writeValueAsString(tableVos.getFields());
            log.info("updateTable:{}", fieldContent);
            tableMapper.updateData(fieldContent, tableVos.getTableComment(),tableName,new Date());

        } catch (SQLException | JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }
    private boolean isPrimaryKeyField(String tableName, String columnName, Connection conn) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
        while (primaryKeys.next()) {
            if (columnName.equals(primaryKeys.getString("COLUMN_NAME"))) {
                return true;
            }
        }
        return false;
    }

    @Override
    public TableVo getTableBytableName(String tableName) {
        TableVo table = tableMapper.getTableBytableName(tableName);
        return table;
    }

    @Override
    public void newUpdateTable(String tableName, Field field) {
        log.info("tableName:{},fieldName:{}", tableName,field);
        TableVo tableVos=tableMapper.getTableBytableName(tableName);
        log.info("updateTable:{}", tableVos);
        try {
            // 修复这里 - 使用TypeReference指定完整类型
            List<Field> fields = new ObjectMapper().readValue(
                    tableVos.getFieldContent(),
                    new TypeReference<List<Field>>() {}
            );
            tableVos.setFields(fields);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
        //遍历fields如果有fieldName传入一样的则在修改的这张表中进行删除没有则添加
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            boolean fieldExists = false;

            // 先检查字段是否存在
            for (Field fields : tableVos.getFields()) {
                if (fields.getFieldName().equals(field.getFieldName())) {
                    fieldExists = true;
                    break;
                }
            }

            if (fieldExists) {
                // 字段存在，执行删除操作
                // 如果是主键，需要先删除主键约束（如果存在）
                if (isPrimaryKeyField(tableName, field.getFieldName(), conn)) {
                    String dropPkSql = "ALTER TABLE " + tableName + " DROP PRIMARY KEY";
                    stmt.executeUpdate(dropPkSql);
                }

                String dropColumnSql = "ALTER TABLE " + tableName +
                        " DROP COLUMN " + field.getFieldName();
                stmt.executeUpdate(dropColumnSql);

                // 从字段列表中移除该字段
                tableVos.getFields().removeIf(fields -> fields.getFieldName().equals(field.getFieldName()));
            } else {
                // 字段不存在，执行添加操作
                Field newField = new Field(); // 假设这是你的字段对象
                // 设置新字段的属性（根据你的实际需求）
                newField.setFieldName(field.getFieldName());
                newField.setFieldType(field.getFieldType());      // 示例类型
                newField.setFieldLength(field.getFieldLength());       // 示例长度
                newField.setFieldDecimal(field.getFieldDecimal());
                newField.setIsNull(field.getIsNull());            // 不允许NULL
                newField.setIsKey(field.getIsKey());             // 设为主键
                newField.setIsAddId(field.getIsAddId());          // 自动递增
                newField.setFieldComment(field.getFieldComment()); // 字段注释

                // 构建添加字段的SQL
                StringBuilder addColumnSql = new StringBuilder("ALTER TABLE ")
                        .append(tableName)
                        .append(" ADD COLUMN ")
                        .append(newField.getFieldName())
                        .append(" ")
                        .append(newField.getFieldType());

                // 处理字段长度和小数位数
                if (newField.getFieldLength() > 0) {
                    addColumnSql.append("(").append(newField.getFieldLength());

                    // 如果有小数位数（fieldDecimal > 0），则添加
                    if (newField.getFieldDecimal() != null && newField.getFieldDecimal() > 0) {
                        addColumnSql.append(",").append(newField.getFieldDecimal());
                    }
                    addColumnSql.append(")");
                }

                // 处理是否允许NULL
                addColumnSql.append(newField.getIsNull() == 1 ? " NULL" : " NOT NULL");

                // 处理自动递增
                if (newField.getIsAddId() == 1) {
                    addColumnSql.append(" AUTO_INCREMENT");
                }

                // 如果有注释
                if (newField.getFieldComment() != null && !newField.getFieldComment().isEmpty()) {
                    addColumnSql.append(" COMMENT '").append(newField.getFieldComment()).append("'");
                }

                stmt.executeUpdate(addColumnSql.toString());

                // 处理主键（需要在添加字段后单独设置）
                if (newField.getIsKey() == 1) {
                    String addPkSql = "ALTER TABLE " + tableName +
                            " ADD PRIMARY KEY (" + newField.getFieldName() + ")";
                    stmt.executeUpdate(addPkSql);
                }

                // 将新字段添加到字段列表
                tableVos.getFields().add(newField);
            }

            // 更新表的元数据信息
            String fieldContent = new ObjectMapper().writeValueAsString(tableVos.getFields());
            log.info("updateTable:{}", fieldContent);
            tableMapper.updateData(fieldContent, tableVos.getTableComment(),tableName,new Date());

        } catch (SQLException | JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void deleteTable(Integer id) {
        String tableName  = tableMapper.selectTableNameById(id);
        log.info("deleteTable:{}", tableName);
        Connection connection = null;
        Statement statement = null;

        try {
            // 1. 建立连接
            connection = DriverManager.getConnection(url, user, password);

            // 2. 创建语句
            statement = connection.createStatement();

            // 3. 执行SQL删除表
            String sql = "DROP TABLE IF EXISTS " + tableName;
            statement.executeUpdate(sql);

            log.info("{}表删除成功", tableName);
        } catch (SQLException e) {
            log.error("Error deleting table {}", tableName, e);
        } finally {
            // 4. Close resources
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                log.error("Error closing database resources", e);
            }
        }
        tableMapper.deleteTable(id);
    }
}
